from PyQt5.QtWidgets import (QApplication,QWidget,QTableWidgetItem,QAbstractItemView,
                             QToolBar,QAction,QTableWidgetItem,QHeaderView,QSizePolicy,QFrame,QMessageBox,
                             QFileDialog,QLineEdit,QMenu)
from PyQt5.QtCore import Qt,QSize,QDateTime,QDate
from PyQt5.QtGui import QIcon,QFont
from functools import partial
import sys
import threading
import os,time
import openpyxl
from forms.component.db import 操作_sql
from forms.component.progressWindow import progress,wrapper_progress
from forms.component import 查询
class 查询_基类_窗体(QWidget):
    def __init__(self,lineedit_text,sql):
        super(查询_基类_窗体, self).__init__()
        self.lineedit_text=lineedit_text
        self.sql=sql

        self.initUi()
        self.ui.checkBox.setVisible(False)
        self.ui.lineEdit_2.setFocus()
    def initUi(self):
        '''窗口的初始化'''
        self.ui = 查询.Ui_Form()
        self.ui.setupUi(self)

        self.初始化()
        self.ui.pushButton_6.clicked.connect(lambda: self.Next_Btn_Clicked(self.current_page, self.items))
        self.ui.pushButton_5.clicked.connect(lambda: self.Next_Btn_Clicked(self.current_page - 2, self.items))
        self.ui.pushButton_4.clicked.connect(lambda: self.Next_Btn_Clicked(0, self.items))
        self.ui.pushButton_7.clicked.connect(lambda: self.Next_Btn_Clicked(len(self.items), self.items))
        self.ui.pushButton_8.clicked.connect(lambda: self.ALL_Btn_Clicked(0, self.items))
        self.ui.comboBox.currentTextChanged.connect(self.page_row_Changed)
        self.ui.comboBox_2.currentTextChanged.connect(self.page_count_Changed)
        self.ui.pushButton.clicked.connect(self.TextChanged)
        self.ui.lineEdit_2.returnPressed.connect(self.TextChanged)
        self.ui.tableWidget.doubleClicked.connect(self.tablewidget_DbClicked)
        self.ui.pushButton_2.clicked.connect(self._auto) #单元格自适应
        # 右键弹出菜单
        self.ui.tableWidget.setContextMenuPolicy(Qt.ContextMenuPolicy.CustomContextMenu)
        # 右键槽函数
        self.ui.tableWidget.customContextMenuRequested.connect(partial(self._generateMenu, self.ui.tableWidget))
    def _generateMenu(self,textedit, pos):
        # print(pos)
        # 转换屏幕的坐标系

        screenPos = self.sender().mapToGlobal(pos)
        # colNum, rowNum = -1, -1
        # for i in self.ui.tableWidget.selectionModel().selection().indexes():
        #     colNum = i.column()
        #     rowNum = i.row()
            # print('选中的单元格', i.column(), i.row())
        # if colNum == 6:
        tabWidget_menu = QMenu()
        sb_item = tabWidget_menu.addAction("导出数据-Excel")


        tabWidget_action = tabWidget_menu.exec(screenPos)  # 右键执行
        if tabWidget_action == sb_item:
            self.导出数据()
    def 导出数据(self):

        base_dir=os.path.dirname(os.path.abspath("__file__"))
        fileName, ok = QFileDialog.getSaveFileName(self, "文件导出",
                                                   f"{base_dir}/temp/导出数据-{QDateTime.currentDateTime().toString('yyyyMMddHHmmss')}",
                                                   "Text Files (*.xlsx);;All Files (*)")

        if ok:
            fileds=[]
            for i in range(self.ui.tableWidget.columnCount()):
                fileds.append(self.ui.tableWidget.horizontalHeaderItem(i).text())


            workbook = openpyxl.Workbook(fileName)
            workbook.create_sheet('导出数据')
            workbook.save(fileName)
            workbook = openpyxl.load_workbook(fileName)
            worksheet = workbook['导出数据']
            worksheet.cell(1,  1, value=f'数据信息导出 {self.windowTitle()}')
            worksheet.merge_cells('A1:T1')

            for i in range(len(fileds)):
                worksheet.cell(2, i + 1, value=fileds[i])
            for i in range(self.ui.tableWidget.rowCount()):
                for j in range(self.ui.tableWidget.columnCount()):
                    try:
                        worksheet.cell(3 + i, j + 1, value=self.ui.tableWidget.item(i,j).text())
                    except:
                        worksheet.cell(3 + i, j + 1, value='')
            # for i in range(len(data)):
            #     for j in range(len(fileds)):
            #         worksheet.cell(2 + i, j + 1, value=data[i][j])

            workbook.save(fileName)
            path = os.path.realpath(fileName)  # 获取的相对路径，并选中文件
            os.system(f'explorer /select,{path}')

    def _auto(self):
        # print('kaishi')

        for i in range(self.ui.tableWidget.rowCount()):
            self.ui.tableWidget.setRowHidden(i, False)
        # self.ui.tableWidget.editItem(self.ui.tableWidget.item(0, 0))
        self.ui.tableWidget.scrollToTop()
        self.ui.tableWidget.resizeColumnsToContents()
        # for i in range(self.ui.tableWidget.columnCount()):
        #     self.ui.tableWidget.resizeColumnToContents(i)
        self.TextChanged()
        # print('jieshu')
    def tablewidget_DbClicked(self):
        pass
        # print()



    def 初始化(self):


        self.ui.comboBox.addItems(['10','20','30','40','50','100','200'])
        self.ui.comboBox.setCurrentText('20')
        self.page_row =int(self.ui.comboBox.currentText())  # 每页显示的行数
        self.ui.comboBox_2.addItems([str(i) for i in range(1, 1000)])
        self.ui.lineEdit_2.setText(self.lineedit_text)
        self.显示查询数据()

    def page_count_Changed(self):


        self.Next_Btn_Clicked(int(self.ui.comboBox_2.currentText())-1, self.items)

    def page_row_Changed(self):
        self.page_row=int(self.ui.comboBox.currentText())
        self.Next_Btn_Clicked(0, self.items)

    @wrapper_progress
    def progress_working(self):
        for i in range(self.ui.tableWidget.rowCount()):
            self.ui.tableWidget.setRowHidden(i, False)
        self.ui.tableWidget.clear()
        self.ui.tableWidget.setSortingEnabled(False)

        # 设置两行间隔色
        self.ui.tableWidget.setAlternatingRowColors(True)

        bool, mesg, data, fileds = 操作_sql().query_one(self.sql)

        if bool and data:

            self.ui.tableWidget.setColumnCount(len(fileds))
            self.ui.tableWidget.setRowCount(len(data))
            # 设置表头
            self.ui.tableWidget.setHorizontalHeaderLabels(fileds)

            # # 添加数据
            for i in range(len(data)):

                for j in range(len(fileds)):
                    if data[i][j]==None:
                        self.ui.tableWidget.setItem(i, j, QTableWidgetItem(''))
                        continue
                    self.ui.tableWidget.setItem(i, j, QTableWidgetItem(str(data[i][j])))

            # self.ui.tableWidget.resizeColumnsToContents()
            for i in range(len(data)):
                self.ui.tableWidget.setRowHidden(i, True)
            self.currend_page = 0
            self.items = [i for i in range(self.ui.tableWidget.rowCount())]

            # 升降序排序
            self.ui.tableWidget.setSortingEnabled(True)

            #设置列宽自适应
            # self.ui.tableWidget.resizeColumnsToContents()

            # 设置整行选择
            self.ui.tableWidget.setSelectionBehavior(QAbstractItemView.SelectionBehavior.SelectRows)

            self.Next_Btn_Clicked(0,self.items)
            # self.TextChanged()

    def 显示查询数据(self):
        self.进度条 = progress()
        self.进度条.show()
        thread1 = threading.Thread(target=self.progress_working)
        thread1.start()




    def ALL_Btn_Clicked(self, current_page, items):
        self.current_page = current_page + 1
        self.items = items
        # print(len(self.items), self.current_page)
        total_row = len(self.items)  # 所有行数
        self.page_row = total_row

        page_ = divmod(total_row, self.page_row)  # 商和余数返回（3，1）
        if page_[1] == 0:
            page_count = page_[0]
        else:
            page_count = page_[0] + 1

        if self.current_page <= 0:
            self.current_page = 1
        if self.current_page >= page_count + 1:
            self.current_page = page_count

        # 隐藏所有
        for i in range(len(self.items)):
            if i >= self.page_row * (self.current_page - 1) and i < self.page_row * (self.current_page):
                self.ui.tableWidget.setRowHidden(self.items[i], False)
            else:
                self.ui.tableWidget.setRowHidden(self.items[i], True)
        # self.ui.tableWidget.scrollToTop()
        # self.ui.tableWidget.resizeColumnsToContents()
        # self.ui.tableWidget.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.ui.label_2.setText(
            "第" + str(self.current_page) + "页/总" + str(page_count) + "页 共" + str(total_row) + "行数据 每页显示" + str(
                self.page_row) + "行数据")
        # self.page_row = 20
    def Next_Btn_Clicked(self, current_page, items):
        self.current_page = current_page + 1
        self.items = items
        # print(len(self.items), self.current_page)
        total_row = len(self.items)  # 所有行数

        page_ = divmod(total_row, self.page_row)  # 商和余数返回（3，1）
        if page_[1] == 0:
            page_count = page_[0]
        else:
            page_count = page_[0] + 1

        if self.current_page <= 0:
            self.current_page = 1
        if self.current_page >= page_count + 1:
            self.current_page = page_count

        for i in range(self.ui.tableWidget.rowCount()):
            self.ui.tableWidget.setRowHidden(i, False)
            # self.ui.tableWidget.editItem(self.ui.tableWidget.item(0, 0))
        self.ui.tableWidget.scrollToTop()
        self.ui.tableWidget.resizeColumnsToContents()
        for i in range(self.ui.tableWidget.rowCount()):
            self.ui.tableWidget.setRowHidden(i, True)

        # 隐藏所有
        for i in range(len(self.items)):
            if  self.page_row * (self.current_page - 1) <= i < self.page_row * (self.current_page):
                self.ui.tableWidget.setRowHidden(self.items[i], False)
            else:
                self.ui.tableWidget.setRowHidden(self.items[i], True)


        tex=f"第{self.current_page}页/总{page_count}页 共{total_row}行数据 每页显示{self.page_row}行数据"
        self.ui.label_2.setText(tex)

    def TextChanged(self):
        # self.cbxtext=self.ui.comboBox.currentText()
        # self.显示数据()
        for i in range(self.ui.tableWidget.rowCount()):
            self.ui.tableWidget.setRowHidden(i, False)
            # self.ui.tableWidget.editItem(self.ui.tableWidget.item(0, 0))
        self.ui.tableWidget.scrollToTop()
        self.ui.tableWidget.resizeColumnsToContents()

        for i in range(self.ui.tableWidget.rowCount()):
            self.ui.tableWidget.setRowHidden(i, True)
        text = self.ui.lineEdit_2.text()  # 筛选 含有 “test” 的所有行
        if text != "":
            items = set()
            items_all = self.ui.tableWidget.findItems(text, Qt.MatchFlag.MatchContains)
            # print(str(items))

            # current_page=1
            # row_page=23

            for item in items_all:
                # print(item.text())
                # print(item.row())
                self.ui.tableWidget.setRowHidden(item.row(), False)
                items.add(item.row())
            self.items = list(items)

        else:
            # items = self.ui.tableWidget.findItems("*", PyQt5.QtCore.Qt.MatchFlag.MatchWildcard)  # 基于通配符
            for i in range(self.ui.tableWidget.rowCount()):
                self.ui.tableWidget.setRowHidden(i, False)
            self.items = [i for i in range(self.ui.tableWidget.rowCount())]
            # self.ui.tableWidget.resizeColumnsToContents()
        self.Next_Btn_Clicked(0, self.items)




if __name__=='__main__':
    app = QApplication(sys.argv)
    登录_ = 查询_基类_窗体(lineedit_text='',sql='select * from 仓库管理_饮片_原料库_入库管理')
    登录_.show()

    sys.exit(app.exec_())
